package com.apes.scm.settle.settle.service;

import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import com.apes.framework.query.ApesDao;
import org.springframework.stereotype.Component;

import java.util.Date;
import java.util.List;
import java.util.Map;

@Component
public class SettleSimpleDao extends ApesDao {

    /**
     * 分页查询
     *
     * @param param
     * @return
     */
    public JSONArray findDept(JSONObject param, String company, int page, int size) {
        StringBuffer sb = new StringBuffer();

        sb.append(" SELECT * FROM ( SELECT CB.*, ROWNUM AS rowno FROM (  SELECT a.id, a.state, a.invoice_state,\n" +
                "       '['|| A.COMPANY_ID || ']' ||(SELECT T.NAME FROM MAS_PARTY_ROLE P,MAS_PARTY T WHERE P.PARTY_ID=T.ID AND P.ID=A.COMPANY_ID) COMPANY_NAME,\n" +
                "        '['|| A.PARTNER_ID || ']' ||(SELECT T.NAME FROM MAS_PARTY_ROLE P,MAS_PARTY T WHERE P.PARTY_ID=T.ID AND P.ID=A.PARTNER_ID) PARTNER_NAME,\n" +
                "        a.dept_id,(SELECT B.NAME FROM MAS_DEPT B WHERE B.ID = A.DEPT_ID) DEPE_NAME,\n" +
                "      (SELECT NAME FROM ACC_COOPERATION WHERE ID = A.COOPERATION_ID) COOPERATION_NAME,TO_CHAR(a.create_date, 'YYYY-MM-DD HH24:MI:SS') create_date, \n" +
                "     TO_CHAR(a.approve_date, 'YYYY-MM-DD HH24:MI:SS') approve_date , a.settle_amount, a.note FROM acc_settle_simple a where 1 = 1 \n");
        if (param.get("ID") != null) {
            sb.append(" AND a.id =:ID");
        }
        if (param.get("partner_id") != null) {
            sb.append(" AND a.PARTNER_ID =:partner_id");
        }
        if (param.get("STATE") != null) {
            sb.append(" AND a.STATE =:STATE");
        }

        if (param.get("invoice_state") != null) {
            sb.append(" AND a.invoice_state =:invoice_state");
        }
        if (param.get("create_date") != null) {
            sb.append(" AND a.create_date >= to_date(substr(:create_date_DATE,1,10),'yyyy-MM-dd') AND a.create_date < to_date(substr(:create_date,1,10),'yyyy-MM-dd') +1");
        }
        if (param.get("approve_date") != null) {
            sb.append(" AND a.approve_date >= to_date(substr(:approve_date_DATE,1,10),'yyyy-MM-dd') AND a.approve_date < to_date(substr(:approve_date,1,10),'yyyy-MM-dd') +1");
        }
        sb.append(" AND ((EXISTS (SELECT 1 " +
                "                FROM AUT_POSTS ROLE1, AUT_LOCAL_PERMIT ROLE2, " +
                "                AUT_LOCAL_PERMIT_ITEM ROLE3 " +
                "                WHERE ROLE1.LOCAL_PERMIT_ID = ROLE2.ID " +
                "                AND ROLE2.ID = ROLE3.LOCAL_PERMIT_ID " +
                "                AND ROLE1.ID = '" + company + "' " +
                "                AND ROLE3.TYPE = 'pt_company' " +
                "                AND ROLE3.PURPOSE = 'pp_search' " +
                "                AND A.COMPANY_ID = ROLE3.ORG_ID) OR " +
                "                (SELECT NVL(COUNT(1), 0) " +
                "                        FROM AUT_POSTS ROLE1, AUT_LOCAL_PERMIT ROLE2, " +
                "                        AUT_LOCAL_PERMIT_ITEM ROLE3 " +
                "                        WHERE ROLE1.LOCAL_PERMIT_ID = ROLE2.ID " +
                "                        AND ROLE2.ID = ROLE3.LOCAL_PERMIT_ID " +
                "                        AND ROLE1.ID = '" + company + "' " +
                "                        AND ROLE3.TYPE = 'pt_company' " +
                "                        AND ROLE3.PURPOSE = 'pp_search') = 0))");
        sb.append(" order by a.create_date desc  ) CB WHERE ROWNUM <=" + (page == 0 ? size : (page + 1) * size) + " )T_CB WHERE T_CB.rowno >" + ((page + 1) - 1) * size);
        return toJSONArray(find(sb.toString(), param));

    }

    public JSONArray findDeptCount(JSONObject param, String company) {
        StringBuffer sb = new StringBuffer();

        sb.append(" SELECT count(1) COUNT FROM acc_settle_simple a where 1 = 1 \n");
        if (param.get("ID") != null) {
            sb.append(" AND a.id =:ID");
        }
        if (param.get("partner_id") != null) {
            sb.append(" AND a.PARTNER_ID =:partner_id");
        }
        if (param.get("STATE") != null) {
            sb.append(" AND a.STATE =:STATE");
        }
        if (param.get("create_date") != null) {
            sb.append(" AND a.create_date >= to_date(substr(:create_date_DATE,1,10),'yyyy-MM-dd') AND a.create_date < to_date(substr(:create_date,1,10),'yyyy-MM-dd') +1");
        }
        if (param.get("approve_date") != null) {
            sb.append(" AND a.approve_date >= to_date(substr(:approve_date_DATE,1,10),'yyyy-MM-dd') AND a.approve_date < to_date(substr(:approve_date,1,10),'yyyy-MM-dd') +1");
        }
        sb.append(" AND ((EXISTS (SELECT 1 " +
                "                FROM AUT_POSTS ROLE1, AUT_LOCAL_PERMIT ROLE2, " +
                "                AUT_LOCAL_PERMIT_ITEM ROLE3 " +
                "                WHERE ROLE1.LOCAL_PERMIT_ID = ROLE2.ID " +
                "                AND ROLE2.ID = ROLE3.LOCAL_PERMIT_ID " +
                "                AND ROLE1.ID = '" + company + "' " +
                "                AND ROLE3.TYPE = 'pt_company' " +
                "                AND ROLE3.PURPOSE = 'pp_search' " +
                "                AND A.COMPANY_ID = ROLE3.ORG_ID) OR " +
                "                (SELECT NVL(COUNT(1), 0) " +
                "                        FROM AUT_POSTS ROLE1, AUT_LOCAL_PERMIT ROLE2, " +
                "                        AUT_LOCAL_PERMIT_ITEM ROLE3 " +
                "                        WHERE ROLE1.LOCAL_PERMIT_ID = ROLE2.ID " +
                "                        AND ROLE2.ID = ROLE3.LOCAL_PERMIT_ID " +
                "                        AND ROLE1.ID = '" + company + "' " +
                "                        AND ROLE3.TYPE = 'pt_company' " +
                "                        AND ROLE3.PURPOSE = 'pp_search') = 0))");
        sb.append(" order by a.create_date desc ");
        return toJSONArray(find(sb.toString(), param));

    }


    /**
     * 分页查询
     *
     * @param param
     * @return
     */
    public Object findCount(JSONObject param) {
        StringBuffer sb = new StringBuffer();

        sb.append("  SELECT count(1) as COUNT FROM acc_settle_simple a where 1 = 1 \n");
        if (param.get("ID") != null) {
            sb.append(" AND a.id =:ID");
        }
        if (param.get("MERCHANT_ID") != null) {
            sb.append(" AND a.MERCHANT_ID =:MERCHANT_ID");
        }
        if (param.get("RECONCILE_UID") != null) {
            sb.append(" AND a.RECONCILE_UID =:RECONCILE_UID");
        }
        if (param.get("STATE") != null) {
            sb.append(" AND a.STATE =:STATE");
        }
        if (param.get("reconcile_begin_date") != null) {
            sb.append(" AND a.reconcile_begin_date >= to_date(substr(:reconcile_begin_date_DATE,1,10),'yyyy-MM-dd') AND a.reconcile_begin_date < to_date(substr(:reconcile_begin_date,1,10),'yyyy-MM-dd') +1");
        }
        if (param.get("reconcile_end_date") != null) {
            sb.append(" AND a.reconcile_end_date >= to_date(substr(:reconcile_end_date_DATE,1,10),'yyyy-MM-dd') AND a.reconcile_end_date < to_date(substr(:reconcile_end_date,1,10),'yyyy-MM-dd') +1");
        }

        if (param.get("reconcile_date") != null) {
            sb.append(" AND a.reconcile_date >= to_date(:reconcile_date_DATE,'yyyy-MM-dd hh24:mi:ss') AND a.reconcile_date < to_date(:reconcile_date,'yyyy-MM-dd hh24:mi:ss')  ");
        }
        sb.append(" order by a.create_date desc ");
        return toJSONArray(find(sb.toString(), param));

    }

    public JSONArray findSettleSimpleItem(String id) {
        StringBuffer sb = new StringBuffer();
        sb.append("  SELECT a.id, a.car_license, a.origin, a.origin_business_type, a.product_uom_id, " +
                "(SELECT b.name FROM mas_product_uom b where a.product_uom_id=b.id) product_uom_name,a.pending_qty, a.actual_qty, a.amount,\n" +
                "       a.occurrence_date, a.source, a.source_business_type,a.product_id,a.repletion_amount,a.free_amount,a.note," +
                "(SELECT b.name FROM mas_product b where a.product_id=b.id) product_name,a.invoice_id  FROM acc_settle_simple_item a where a.settle_simple_id = '" + id + "'");
        return toJSONArray(find(sb.toString()));

    }

    public JSONArray findSettleSimplePaymentMethod(String id) {
        StringBuffer sb = new StringBuffer();
        sb.append("SELECT a.payment_method_type,a.bank_account_id,a.amount,a.note FROM acc_settle_simple_payment a where a.settle_simple_id= '" + id + "'");
        return toJSONArray(find(sb.toString()));
    }

    public List<Map> accountsReceivable(JSONObject param, String company, int page, int size) {
        StringBuffer sb = new StringBuffer();
        sb.append("SELECT * FROM (SELECT CB.*, ROWNUM AS rowno FROM (SELECT t.create_date,add_months(to_date(t.create_date, 'YYYY-MM'), 1) - 1 max_data,\n" +
                "       to_date(t.concat_date, 'yyyy-mm-dd') current_date,to_date(t.concat_date, 'yyyy-mm-dd') - 1 concat_date, t.company_id,\n" +
                "       t.partner_id, sum(t.PRICE_SUBTOTAL) PRICE_SUBTOTAL,sum(free_amount) free_amount, sum(repletion_amount) repletion_amount,\n" +
                "       sum(settle_amount) settle_amount, sum(amount) amount\n" +
                "  FROM (SELECT to_char(a.create_date, 'yyyy-mm') create_date,concat(to_char(a.create_date, 'yyyy-mm'), '-01') concat_date,\n" +
                "                a.company_id, a.partner_id,sum(b.PRICE_SUBTOTAL) PRICE_SUBTOTAL, 0 free_amount,0 repletion_amount, 0 settle_amount, 0 amount\n" +
                "           FROM acc_invoice a LEFT JOIN ACC_INVOICE_ITEM b ON a.ID = b.INVOICE_ID join MAS_PARTY_ROLE r\n" +
                "             on A.PARTNER_ID = R.ID where R.ROLE_ID = 'Customer' and a.create_date is not null and a.company_id = :COMPANY_ID\n" +
                "            AND a.create_date >= to_date(:create_date_DATE,'yyyy-mm-dd HH24:MI:SS') AND a.create_date < to_date(:create_date,'yyyy-mm-dd HH24:MI:SS')" +
                "            and a.partner_id = :PARTNER_ID group by to_char(a.create_date, 'yyyy-mm'), a.company_id,a.partner_id\n" +
                "         UNION ALL\n" +
                "         SELECT to_char(a.APPROVE_DATE, 'yyyy-mm') create_date, concat(to_char(a.APPROVE_DATE, 'yyyy-mm'), '-01') concat_date,\n" +
                "                a.company_id, a.partner_id, 0 PRICE_SUBTOTAL, sum(a.free_amount) free_amount, (sum(a.settle_amount) - sum(a.free_amount)) repletion_amount,\n" +
                "                sum(a.settle_amount) settle_amount, 0 amount\n" +
                "           FROM ACC_SETTLE_SIMPLE a WHERE a.STATE = 'approve' and a.APPROVE_DATE is not null and a.company_id = :COMPANY_ID\n" +
                "            AND a.APPROVE_DATE >= to_date(:create_date_DATE,'yyyy-mm-dd HH24:MI:SS') AND a.APPROVE_DATE < to_date(:create_date,'yyyy-mm-dd HH24:MI:SS')" +
                "            and a.partner_id = :PARTNER_ID group by to_char(a.APPROVE_DATE, 'yyyy-mm'), a.PARTNER_ID, a.company_id\n" +
                "         UNION ALL\n" +
                "         SELECT to_char(d.create_date, 'yyyy-mm') create_date, concat(to_char(sysdate, 'yyyy-mm'), '-01') concat_date,\n" +
                "                d.company_id, d.partner_id, 0 PRICE_SUBTOTAL, 0 free_amount, 0 repletion_amount, 0 settle_amount, sum(d.amount)\n" +
                "           FROM (SELECT b.create_date, a.company_id, a.partner_id, b.amount FROM acc_ledger a, acc_ledger_entry b\n" +
                "                   where a.id = b.ledger_id and a.ledger_account_id = 'current_unsettled'\n" +
                "            AND a.create_date >= to_date(:create_date_DATE,'yyyy-mm-dd HH24:MI:SS') AND a.create_date < to_date(:create_date,'yyyy-mm-dd HH24:MI:SS')" +
                "                     and a.company_id = :COMPANY_ID and a.partner_id = :PARTNER_ID order by b.create_date desc) d\n" +
                "          where rownum = 1 group by to_char(d.create_date, 'yyyy-mm'), d.company_id, d.partner_id) t where 1=1\n");
        sb.append(" AND ((EXISTS (SELECT 1 " +
                "                FROM AUT_POSTS ROLE1, AUT_LOCAL_PERMIT ROLE2, " +
                "                AUT_LOCAL_PERMIT_ITEM ROLE3 " +
                "                WHERE ROLE1.LOCAL_PERMIT_ID = ROLE2.ID " +
                "                AND ROLE2.ID = ROLE3.LOCAL_PERMIT_ID " +
                "                AND ROLE1.ID = '" + company + "' " +
                "                AND ROLE3.TYPE = 'pt_company' " +
                "                AND ROLE3.PURPOSE = 'pp_search' " +
                "                AND t.COMPANY_ID = ROLE3.ORG_ID) OR " +
                "                (SELECT NVL(COUNT(1), 0) " +
                "                        FROM AUT_POSTS ROLE1, AUT_LOCAL_PERMIT ROLE2, " +
                "                        AUT_LOCAL_PERMIT_ITEM ROLE3 " +
                "                        WHERE ROLE1.LOCAL_PERMIT_ID = ROLE2.ID " +
                "                        AND ROLE2.ID = ROLE3.LOCAL_PERMIT_ID " +
                "                        AND ROLE1.ID = '" + company + "' " +
                "                        AND ROLE3.TYPE = 'pt_company' " +
                "                        AND ROLE3.PURPOSE = 'pp_search') = 0))");
        sb.append(" group by t.create_date, t.concat_date, t.company_id, t.partner_id order by t.create_date desc) " +
                "CB WHERE ROWNUM <=" + (page == 0 ? size : (page + 1) * size) + " )T_CB WHERE T_CB.rowno >" + ((page + 1) - 1) * size);
        return this.find(sb.toString(), param);
    }

    public JSONArray accountsReceivableCount(JSONObject param, String company) {
        StringBuffer sb = new StringBuffer();
        sb.append("SELECT count(1) as count\n" +
                "  FROM (SELECT to_char(a.create_date, 'yyyy-mm') create_date,concat(to_char(a.create_date, 'yyyy-mm'), '-01') concat_date,\n" +
                "                a.company_id, a.partner_id,sum(b.PRICE_SUBTOTAL) PRICE_SUBTOTAL, 0 free_amount,0 repletion_amount, 0 settle_amount, 0 amount\n" +
                "           FROM acc_invoice a LEFT JOIN ACC_INVOICE_ITEM b ON a.ID = b.INVOICE_ID join MAS_PARTY_ROLE r\n" +
                "             on A.PARTNER_ID = R.ID where R.ROLE_ID = 'Customer' and a.create_date is not null and a.company_id = :COMPANY_ID\n" +
                "            AND a.create_date >= to_date(:create_date_DATE,'yyyy-mm-dd HH24:MI:SS') AND a.create_date < to_date(:create_date,'yyyy-mm-dd HH24:MI:SS')" +
                "            and a.partner_id = :PARTNER_ID group by to_char(a.create_date, 'yyyy-mm'), a.company_id,a.partner_id\n" +
                "         UNION ALL\n" +
                "         SELECT to_char(a.APPROVE_DATE, 'yyyy-mm') create_date, concat(to_char(a.APPROVE_DATE, 'yyyy-mm'), '-01') concat_date,\n" +
                "                a.company_id, a.partner_id, 0 PRICE_SUBTOTAL, sum(a.free_amount) free_amount, (sum(a.settle_amount) - sum(a.free_amount)) repletion_amount,\n" +
                "                sum(a.settle_amount) settle_amount, 0 amount\n" +
                "           FROM ACC_SETTLE_SIMPLE a WHERE a.STATE = 'approve' and a.APPROVE_DATE is not null and a.company_id = :COMPANY_ID\n" +
                "            AND a.APPROVE_DATE >= to_date(:create_date_DATE,'yyyy-mm-dd HH24:MI:SS') AND a.APPROVE_DATE < to_date(:create_date,'yyyy-mm-dd HH24:MI:SS')" +
                "            and a.partner_id = :PARTNER_ID group by to_char(a.APPROVE_DATE, 'yyyy-mm'), a.PARTNER_ID, a.company_id\n" +
                "         UNION ALL\n" +
                "         SELECT to_char(d.create_date, 'yyyy-mm') create_date, concat(to_char(sysdate, 'yyyy-mm'), '-01') concat_date,\n" +
                "                d.company_id, d.partner_id, 0 PRICE_SUBTOTAL, 0 free_amount, 0 repletion_amount, 0 settle_amount, sum(d.amount)\n" +
                "           FROM (SELECT b.create_date, a.company_id, a.partner_id, b.amount FROM acc_ledger a, acc_ledger_entry b\n" +
                "                   where a.id = b.ledger_id and a.ledger_account_id = 'current_unsettled'\n" +
                "            AND a.create_date >= to_date(:create_date_DATE,'yyyy-mm-dd HH24:MI:SS') AND a.create_date < to_date(:create_date,'yyyy-mm-dd HH24:MI:SS')" +
                "                     and a.company_id = :COMPANY_ID and a.partner_id = :PARTNER_ID order by b.create_date desc) d\n" +
                "          where rownum = 1 group by to_char(d.create_date, 'yyyy-mm'), d.company_id, d.partner_id) t where 1=1\n");
        sb.append(" AND ((EXISTS (SELECT 1 " +
                "                FROM AUT_POSTS ROLE1, AUT_LOCAL_PERMIT ROLE2, " +
                "                AUT_LOCAL_PERMIT_ITEM ROLE3 " +
                "                WHERE ROLE1.LOCAL_PERMIT_ID = ROLE2.ID " +
                "                AND ROLE2.ID = ROLE3.LOCAL_PERMIT_ID " +
                "                AND ROLE1.ID = '" + company + "' " +
                "                AND ROLE3.TYPE = 'pt_company' " +
                "                AND ROLE3.PURPOSE = 'pp_search' " +
                "                AND t.COMPANY_ID = ROLE3.ORG_ID) OR " +
                "                (SELECT NVL(COUNT(1), 0) " +
                "                        FROM AUT_POSTS ROLE1, AUT_LOCAL_PERMIT ROLE2, " +
                "                        AUT_LOCAL_PERMIT_ITEM ROLE3 " +
                "                        WHERE ROLE1.LOCAL_PERMIT_ID = ROLE2.ID " +
                "                        AND ROLE2.ID = ROLE3.LOCAL_PERMIT_ID " +
                "                        AND ROLE1.ID = '" + company + "' " +
                "                        AND ROLE3.TYPE = 'pt_company' " +
                "                        AND ROLE3.PURPOSE = 'pp_search') = 0))");
        sb.append(" group by t.create_date, t.concat_date, t.company_id, t.partner_id order by t.create_date desc");
        return toJSONArray(find(sb.toString(), param));
    }

    public List<Map> accountsReceivableSummary(JSONObject param, String company, int page, int size) {
        StringBuffer sb = new StringBuffer();
        sb.append("SELECT * FROM (SELECT CB.*, ROWNUM AS rowno FROM (SELECT t.create_date, add_months(trunc(sysDate, 'yyyy'),12)-1 max_data," +
                "to_date(t.concat_date, 'yyyy-mm-dd') current_date,to_date(t.concat_date, 'yyyy-mm-dd') - 1 concat_date, t.company_id,t.partner_id, sum(t.PRICE_SUBTOTAL) PRICE_SUBTOTAL,\n" +
                "       sum(free_amount) free_amount, sum(repletion_amount) repletion_amount,sum(settle_amount) settle_amount\n" +
                "  FROM (SELECT NVL(to_char(a.create_date, 'yyyy'),to_char(sysdate, 'yyyy')) create_date,\n" +
                "                concat(NVL(to_char(a.create_date, 'yyyy'),to_char(sysdate, 'yyyy')), '-01-01') concat_date,\n" +
                "                a.company_id, a.partner_id,\n" +
                "                sum(b.PRICE_SUBTOTAL) PRICE_SUBTOTAL, 0 free_amount,\n" +
                "                0 repletion_amount, 0 settle_amount\n" +
                "           FROM acc_invoice a  \n" +
                "           LEFT JOIN ACC_INVOICE_ITEM b\n" +
                "             ON a.ID = b.INVOICE_ID\n" +
                "           join MAS_PARTY_ROLE r\n" +
                "             on A.PARTNER_ID = R.ID\n" +
                "          where R.ROLE_ID = 'Customer'\n" +
                "          group by to_char(a.create_date, 'yyyy'), a.company_id, a.partner_id\n" +
                "         UNION ALL\n" +
                "         SELECT NVL(to_char(a.APPROVE_DATE, 'yyyy'),to_char(sysdate, 'yyyy')) create_date,\n" +
                "                concat(NVL(to_char(a.APPROVE_DATE, 'yyyy'),to_char(sysdate, 'yyyy')), '-01-01') concat_date,\n" +
                "                a.company_id, a.partner_id, 0 PRICE_SUBTOTAL,\n" +
                "                sum(a.free_amount) free_amount,\n" +
                "                (sum(a.settle_amount) - sum(a.free_amount)) repletion_amount,\n" +
                "                sum(a.settle_amount) settle_amount\n" +
                "           FROM ACC_SETTLE_SIMPLE a\n" +
                "          WHERE a.STATE = 'approve'\n" +
                "          group by to_char(a.APPROVE_DATE, 'yyyy'), a.PARTNER_ID, a.company_id) t where 1=1\n");
        if (param.get("COMPANY_ID") != null) {
            sb.append(" AND t.company_id =:COMPANY_ID");
        }
        if (param.get("PARTNER_ID") != null) {
            sb.append(" AND t.partner_id =:PARTNER_ID");
        }
        if (param.get("create_date") != null) {
            sb.append(" AND t.create_date = :create_date_date1");
        }
        sb.append(" AND ((EXISTS (SELECT 1 " +
                "                FROM AUT_POSTS ROLE1, AUT_LOCAL_PERMIT ROLE2, " +
                "                AUT_LOCAL_PERMIT_ITEM ROLE3 " +
                "                WHERE ROLE1.LOCAL_PERMIT_ID = ROLE2.ID " +
                "                AND ROLE2.ID = ROLE3.LOCAL_PERMIT_ID " +
                "                AND ROLE1.ID = '" + company + "' " +
                "                AND ROLE3.TYPE = 'pt_company' " +
                "                AND ROLE3.PURPOSE = 'pp_search' " +
                "                AND t.COMPANY_ID = ROLE3.ORG_ID) OR " +
                "                (SELECT NVL(COUNT(1), 0) " +
                "                        FROM AUT_POSTS ROLE1, AUT_LOCAL_PERMIT ROLE2, " +
                "                        AUT_LOCAL_PERMIT_ITEM ROLE3 " +
                "                        WHERE ROLE1.LOCAL_PERMIT_ID = ROLE2.ID " +
                "                        AND ROLE2.ID = ROLE3.LOCAL_PERMIT_ID " +
                "                        AND ROLE1.ID = '" + company + "' " +
                "                        AND ROLE3.TYPE = 'pt_company' " +
                "                        AND ROLE3.PURPOSE = 'pp_search') = 0))");
        sb.append(" group by t.create_date, t.concat_date, t.company_id, t.partner_id order by t.create_date desc) " +
                "CB WHERE ROWNUM <=" + (page == 0 ? size : (page + 1) * size) + " )T_CB WHERE T_CB.rowno >" + ((page + 1) - 1) * size);
        return this.find(sb.toString(), param);
    }

    public JSONArray accountsReceivableSummaryCount(JSONObject param, String company) {
        StringBuffer sb = new StringBuffer();
        sb.append("SELECT count(1) as COUNT  FROM (SELECT NVL(to_char(a.create_date, 'yyyy'),to_char(sysdate, 'yyyy')) create_date,\n" +
                "                concat(NVL(to_char(a.create_date, 'yyyy'),to_char(sysdate, 'yyyy')), '-01-01') concat_date,\n" +
                "                a.company_id, a.partner_id,\n" +
                "                sum(b.PRICE_SUBTOTAL) PRICE_SUBTOTAL, 0 free_amount,\n" +
                "                0 repletion_amount, 0 settle_amount\n" +
                "           FROM acc_invoice a\n" +
                "           LEFT JOIN ACC_INVOICE_ITEM b\n" +
                "             ON a.ID = b.INVOICE_ID\n" +
                "           join MAS_PARTY_ROLE r\n" +
                "             on A.PARTNER_ID = R.ID\n" +
                "          where R.ROLE_ID = 'Customer'\n" +
                "          group by to_char(a.create_date, 'yyyy'), a.company_id, a.partner_id\n" +
                "         UNION ALL\n" +
                "         SELECT NVL(to_char(a.APPROVE_DATE, 'yyyy'),to_char(sysdate, 'yyyy')) create_date,\n" +
                "                concat(NVL(to_char(a.APPROVE_DATE, 'yyyy'),to_char(sysdate, 'yyyy')), '-01-01') concat_date,\n" +
                "                a.company_id, a.partner_id, 0 PRICE_SUBTOTAL,\n" +
                "                sum(a.free_amount) free_amount,\n" +
                "                (sum(a.settle_amount) - sum(a.free_amount)) repletion_amount,\n" +
                "                sum(a.settle_amount) settle_amount\n" +
                "           FROM ACC_SETTLE_SIMPLE a\n" +
                "          WHERE a.STATE = 'approve'\n" +
                "          group by to_char(a.APPROVE_DATE, 'yyyy'), a.PARTNER_ID, a.company_id) t where 1=1\n");
        if (param.get("COMPANY_ID") != null) {
            sb.append(" AND t.company_id =:COMPANY_ID");
        }
        if (param.get("PARTNER_ID") != null) {
            sb.append(" AND t.partner_id =:PARTNER_ID");
        }
        if (param.get("create_date") != null) {
            sb.append(" AND t.create_date = :create_date_date1");
        }
        sb.append(" AND ((EXISTS (SELECT 1 " +
                "                FROM AUT_POSTS ROLE1, AUT_LOCAL_PERMIT ROLE2, " +
                "                AUT_LOCAL_PERMIT_ITEM ROLE3 " +
                "                WHERE ROLE1.LOCAL_PERMIT_ID = ROLE2.ID " +
                "                AND ROLE2.ID = ROLE3.LOCAL_PERMIT_ID " +
                "                AND ROLE1.ID = '" + company + "' " +
                "                AND ROLE3.TYPE = 'pt_company' " +
                "                AND ROLE3.PURPOSE = 'pp_search' " +
                "                AND t.COMPANY_ID = ROLE3.ORG_ID) OR " +
                "                (SELECT NVL(COUNT(1), 0) " +
                "                        FROM AUT_POSTS ROLE1, AUT_LOCAL_PERMIT ROLE2, " +
                "                        AUT_LOCAL_PERMIT_ITEM ROLE3 " +
                "                        WHERE ROLE1.LOCAL_PERMIT_ID = ROLE2.ID " +
                "                        AND ROLE2.ID = ROLE3.LOCAL_PERMIT_ID " +
                "                        AND ROLE1.ID = '" + company + "' " +
                "                        AND ROLE3.TYPE = 'pt_company' " +
                "                        AND ROLE3.PURPOSE = 'pp_search') = 0))");
        sb.append(" group by t.create_date, t.concat_date, t.company_id, t.partner_id order by t.create_date desc");
        return toJSONArray(find(sb.toString(), param));
    }

    public Object findLedger(String company_id, String partner_id, String createDate) {
        String sb = "select t.amount from (select b.id,b.amount,a.company_id,a.partner_id,b.create_date\n" +
                "  from acc_ledger a, acc_ledger_entry b\n" +
                " where a.id = b.ledger_id\n" +
                " and a.ledger_account_id = 'current_unsettled'\n" +
                "   and a.company_id = '" + company_id + "'" +
                "   and a.partner_id = '" + partner_id + "'" +
                "   and b.create_date <= to_date(substr('" + createDate + "',1,10), 'yyyy-mm-dd') - 1 order by b.create_date desc) t where ROWNUM = 1 ";
        return toJSONArray(find(sb));
    }
}
